package com.humi.partner.manager.util;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.alibaba.druid.util.StringUtils;
import com.google.common.collect.Lists;
import com.humi.cloud.common.exception.HumiRuntimeException;
import com.humi.cloud.common.model.ResponseCode;
import com.humi.partner.common.utils.po.ExcelColumnPO;
import com.humi.partner.common.utils.po.ExcelExportPO;

public class ExportExcel {

	/**
	 * 
	 * <pre>
	 * TODO
	 * </pre>
	 * 
	 * @param info
	 * @return excel文件字节数组
	 */
	public static byte[] exportTemplate(ExcelExportPO info) {
		List<ExcelColumnPO> selectShows = new ArrayList<ExcelColumnPO>();
		String[] columns = new String[info.getColumns().size()];
		for (ExcelColumnPO col : info.getColumns()) {
			columns[col.getColumnIndex() - 1] = col.getColumnTitle();
			if (col.getSelectShow() == 1) {
				selectShows.add(col);
			}
		}

		// 声明导出模版工作簿
		XSSFWorkbook template = new XSSFWorkbook();
		XSSFSheet sheet = exportTemplateSheet(template, info.getTitle(), columns);

		for (ExcelColumnPO col : selectShows) {
			makeDataValidation(sheet, col.getSelectItems(), 3, col.getColumnIndex(), 10);
		}
		for (ExcelColumnPO col : info.getColumns()) {
			if ("Date".equals(col.getFieldType())) {
				XSSFCellStyle style = template.createCellStyle(); // 样式对象
				XSSFDataFormat format = template.createDataFormat();
				style.setDataFormat(format.getFormat(col.getFormatStr()));
				setCellStyle(sheet, 3, 10, col.getColumnIndex(), style);
			}
		}
		return workbookToBytes(template);
	}

	private static void setCellStyle(XSSFSheet sheet, int startRow, int endRow, int cellIndex, XSSFCellStyle style) {
		if (startRow < 3) {
			return;
		}
		for (int i = startRow; i < endRow; i++) {
			XSSFCell cell = sheet.createRow(i).createCell(cellIndex);
			cell.setCellStyle(style);
		}
	}

	/**
	 * 
	 * <pre>
	 * 导出模板
	 * </pre>
	 * 
	 * @param titleName 表格标题
	 * @param titles    表格列名
	 * @return 表格字节数组
	 */
	public static byte[] exportTemplate(String titleName, String[] titles) {
		if (StringUtils.isEmpty(titleName)) {
			titleName = "数据导出";
		}
		// 声明导出模版工作簿
		XSSFWorkbook template = new XSSFWorkbook();
		// 生成一个sheet表格
		exportTemplateSheet(template, titleName, titles);
		return workbookToBytes(template);
	}

	/**
	 * 
	 * <pre>
	 * 转换成字节数组
	 * </pre>
	 * 
	 * @param template 工作book
	 * @return 字节数组
	 */
	public static byte[] workbookToBytes(XSSFWorkbook template) {
		ByteArrayOutputStream out = null;
		if (template == null) {
			return null;
		}
		try {
			// 生成临时文件
			out = new ByteArrayOutputStream();
			template.write(out);
			out.close();
			template.close();
		} catch (IOException e) {
			throw HumiRuntimeException.newInstance(ResponseCode.FAILED, e.getMessage());
		}
		return out.toByteArray();
	}

	/**
	 * 
	 * <pre>
	 * 生成选择的下拉选择框
	 * </pre>
	 * 
	 * @param xssfSheet     当前sheet
	 * @param startRowIndex 开始的行数 从1开始
	 * @param columnIndex   第几列 从1开始
	 */
	public static void makeDataValidation(XSSFSheet xssfSheet, String[] items, int startRow, int colIndex,
			int fillRows) {
		if (items == null || items.length == 0) {
			return;
		}
		if (startRow < 1 || colIndex < 1) {
			return;
		}
		colIndex -= 1;
		if (fillRows < startRow) {
			fillRows = startRow + 10;
		}
		XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(xssfSheet);
		XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
				.createExplicitListConstraint(items);
		for (int i = startRow; i < fillRows; i++) {
			CellRangeAddressList addressList = new CellRangeAddressList(i, i, colIndex, colIndex);
			XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
			xssfSheet.addValidationData(validation);
		}
	}

	/**
	 * 
	 * <pre>
	 * 导出模板
	 * </pre>
	 * 
	 * @param template  Workbook
	 * @param titleName 表格标题
	 * @param titles    表格列名
	 * @return 工作sheet
	 */
	public static XSSFSheet exportTemplateSheet(XSSFWorkbook template, String titleName, String[] titles) {
		if (StringUtils.isEmpty(titleName)) {
			titleName = "数据导出";
		}

		// 生成一个表格
		XSSFSheet sheet = template.createSheet();
		// 设置标题样式
		XSSFCellStyle cellTitleStyle = template.createCellStyle();
		cellTitleStyle.setAlignment(HorizontalAlignment.CENTER);
		cellTitleStyle.setVerticalAlignment(VerticalAlignment.CENTER);

		// 设置标题字体
		XSSFFont font = template.createFont();
		font.setFontHeightInPoints((short) 18); // 字体大小
		font.setFontName("微软雅黑"); // 字体类型
		cellTitleStyle.setFont(font);

		if (titles == null || titles.length == 0) {
			throw HumiRuntimeException.newInstance(ResponseCode.FAILED, "未发现有效列");
		}

		// 合并单元格，从0-1行，0-size列
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, titles.length - 1));
		XSSFCell nowCell = sheet.createRow(0).createCell(0);
		nowCell.setCellValue(titleName);
		nowCell.setCellStyle(cellTitleStyle);
		// 设置表头样式
		XSSFCellStyle cellHeaderStyle = template.createCellStyle();
		// 设置表头字体
		XSSFFont headerfont = template.createFont();
		headerfont.setFontHeightInPoints((short) 12); // 字体大小
		headerfont.setBold(true);
		headerfont.setFontName("微软雅黑"); // 字体类型
		cellHeaderStyle.setFont(headerfont);

		// 产生表格标题行
		XSSFRow row = sheet.createRow(2);

		for (int i = 0; i < titles.length; i++) {
			XSSFCell cell = row.createCell(i);
			cell.setCellValue(titles[i]);
			// 设置表头格式
			cell.setCellStyle(cellHeaderStyle);
			// 表头宽度
			sheet.setColumnWidth(i, titles[i].getBytes().length * 256);
		}

		return sheet;
	}

	/**
	 * 
	 * <pre>
	 * 导出数据
	 * </pre>
	 * 
	 * @param data     数据列
	 * @param template 模板数据
	 * @return
	 */
	public static byte[] exportExcel(List<List<String>> data, byte[] template) {
		if (template == null) {
			throw HumiRuntimeException.newInstance(ResponseCode.FAILED, "请先创建模板后再导出");
		}
		if (data == null) {
			throw HumiRuntimeException.newInstance(ResponseCode.FAILED, "请选择需要导出的数据");
		}
		ByteArrayOutputStream out = null;
		InputStream is = null;
		XSSFWorkbook exportWorkbook = null;
		try {
			// 创建模版
			is = new ByteArrayInputStream(template);
			exportWorkbook = new XSSFWorkbook(is);
			XSSFSheet sheet = exportWorkbook.getSheetAt(0);

			List<Integer> maxColumnWidths = Lists.newArrayList();
			for (int i = 0; i < data.get(0).size(); i++) {
				maxColumnWidths.add(sheet.getColumnWidth(i));
			}
			int dataNum = data.size();
			for (int i = 0; i < dataNum; i++) {
				List<String> rows = data.get(i);

				XSSFRow row = sheet.createRow(i + 3);
				for (int j = 0; j < rows.size(); j++) {
					int width = 0;
					XSSFCell cell = row.createCell(j);
					String value = rows.get(j);
					cell.setCellValue(value);
					if (!StringUtils.isEmpty(value)) {
						width = value.getBytes().length * 256;
					}
					if (maxColumnWidths.get(j) == null || maxColumnWidths.get(j) < width) {
						maxColumnWidths.set(j, width);
					}
				}
			}
			// 调整宽度
			for (int ic = 0; ic < maxColumnWidths.size(); ic++) {
				sheet.setColumnWidth(ic, maxColumnWidths.get(ic));
			}
			// 输出
			out = new ByteArrayOutputStream();
			exportWorkbook.write(out);
			out.close();
			return out.toByteArray();
		} catch (Exception e) {
			throw HumiRuntimeException.newInstance(ResponseCode.FAILED, e.getMessage());
		} finally {
			if (exportWorkbook != null) {
				try {
					exportWorkbook.close();
				} catch (IOException e) {
				}
			}
			if (is != null) {
				try {
					is.close();
				} catch (IOException e) {
				}
			}
		}
	}

	/**
	 * 导出Excel
	 * 
	 * @param sheetName sheet名称
	 * @param title     标题
	 * @param values    内容
	 * @param wb        HSSFWorkbook对象
	 * @return
	 */
//	public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, List<List<String>> values,
//			HSSFWorkbook wb) {
//
//		// 第一步，创建一个HSSFWorkbook，对应一个Excel文件
//		if (wb == null) {
//			wb = new HSSFWorkbook();
//		}
//
//		// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
//		HSSFSheet sheet = wb.createSheet(sheetName);
//
//		// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
//		HSSFRow row = sheet.createRow(0);
//
//		// 第四步，创建单元格，并设置值表头 设置表头居中
//		HSSFCellStyle style = wb.createCellStyle();
//		// style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
//
//		// 声明列对象
//		HSSFCell cell = null;
//
//		// 创建标题
//		for (int i = 0; i < title.length; i++) {
//			cell = row.createCell(i);
//			cell.setCellValue(title[i]);
//			cell.setCellStyle(style);
//		}
//
//		// 创建内容
//		for (int i = 0; i < values.size(); i++) {
//			row = sheet.createRow(i + 1);
//			for (int j = 0; j < values.get(i).size(); j++) {
//				// 将内容按顺序赋给对应的列对象
//				row.createCell(j).setCellValue(values.get(i).get(j));
//			}
//		}
//		return wb;
//	}
}
